import re
import time
from flask import Flask, request, jsonify, render_template, send_from_directory
import mysql.connector
import os
import uuid
import base64
import requests
from datetime import datetime
import json
from flask_cors import CORS
from werkzeug.utils import secure_filename
from decimal import Decimal, InvalidOperation

app = Flask(__name__)
CORS(app, resources={r"/api/*": {"origins": "*", "methods": ["GET", "POST"]}})

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': '171525',
    'database': 'invoice_management',
    'port': '3306',
    'charset': 'utf8mb4'
}

# API配置
API_KEY = " "
BASE_URL = "https://dashscope.aliyuncs.com/compatible-mode/v1"

# 文件上传配置
UPLOAD_FOLDER = 'uploads'
ALLOWED_EXTENSIONS = {'png', 'jpg', 'jpeg'}
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

# 检查文件扩展名是否允许
def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

# 确保用户存在
def ensure_user_exists(user_id):
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        user = cursor.fetchone()
        if not user:
            # 根据截图中的信息，我们可以为新建用户设置一些默认值
            cursor.execute(
                """
                INSERT INTO users (id, username, password, company_name, phone, bank_name, bank_account)
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                """,
                (user_id, 'test123', 'test123', '中南财大会计学院', '123456789', '工商银行', '2345678910')
            )
            conn.commit()
        cursor.close()
        conn.close()
    except Exception as e:
        print(f"确保用户存在时出错: {str(e)}")
        raise

# 获取个人信息
@app.route('/api/profile', methods=['GET'])
def get_profile():
    try:
        user_id = 1  # 根据需求固定获取id为1的用户
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        user = cursor.fetchone()
        cursor.close()
        conn.close()

        if user:
            # 处理银行卡号脱敏
            bank_account = user['bank_account']
            masked_account = bank_account[:2] + '*' * (len(bank_account)-2) if bank_account else ''
            user['bank_account'] = masked_account

            return jsonify({'success': True, 'user': user})
        return jsonify({'success': False, 'message': '用户不存在'}), 404
    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({'success': False, 'message': '查询失败'}), 500

# 上传发票图片
@app.route('/api/upload', methods=['POST'])
def upload_invoice():
    try:
        # 确保用户存在
        user_id = 147852369
        ensure_user_exists(user_id)

        if 'file' not in request.files:
            return jsonify({'success': False, 'message': '未上传文件'}), 400

        file = request.files['file']
        if file.filename == '':
            return jsonify({'success': False, 'message': '未选择文件'}), 400

        if file and allowed_file(file.filename):
            filename = secure_filename(file.filename)
            file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
            file.save(file_path)

            image_id = str(uuid.uuid4())
            upload_date = datetime.now().strftime('%Y-%m-%d')

            # 获取用户的 username
            conn = mysql.connector.connect(**DB_CONFIG)
            cursor = conn.cursor()
            cursor.execute("SELECT username FROM users WHERE id = %s", (user_id,))
            user = cursor.fetchone()
            if user:
                username = user[0]
            else:
                return jsonify({'success': False, 'message': '未找到用户信息'}), 404

            # 将图片信息存入数据库，包括文件名
            cursor.execute(
                "INSERT INTO images (image_id, filename, upload_date, uploader) VALUES (%s, %s, %s, %s)",
                (image_id, filename, upload_date, username)
            )
            conn.commit()
            cursor.close()
            conn.close()

            # 调用API识别发票
            invoice_info = call_qwen_api(file_path)
            # 保存发票信息到数据库，包括图片文件名
            save_invoice_info(invoice_info, image_id, filename)

            return jsonify({'success': True, 'message': '发票上传成功', 'data': invoice_info})
        else:
            return jsonify({'success': False, 'message': '不支持的文件格式'}), 400
    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({'success': False, 'message': f'上传过程中发生错误: {str(e)}'}), 500
    
# 调用Qwen API识别发票
def call_qwen_api(file_path):
    max_retries = 3
    retry_delay = 5  # 重试间隔时间（秒）

    for retry in range(max_retries):
        try:
            with open(file_path, "rb") as image_file:
                image_data = base64.b64encode(image_file.read()).decode('utf-8')

            headers = {
                "Authorization": f"Bearer {API_KEY}",
                "Content-Type": "application/json"
            }

            payload = {
                "model": "qwen-vl-plus",
                "messages": [
                    {
                        "role": "user",
                        "content": [
                            {
                                "type": "text",
                                "text": "请准确识别以下字段：\n"
                                        "- 发票号码：20位数字\n"
                                        "- 开票日期：格式如2025年03月01日\n"
                                        "- 销售方名称：在“名称”后的公司全称\n"
                                        "- 纳税人识别号：18位统一信用代码（可能含大写字母）\n"
                                        "- 价税合计（小写）：如￥216.88\n"
                                        "请直接提取值，不要添加说明。"
                            },
                            {"type": "image_url", "image_url": {"url": f"data:image/jpeg;base64,{image_data}"}}
                        ]
                    }
                ]
            }

            response = requests.post(f"{BASE_URL}/chat/completions", json=payload, headers=headers, timeout=30)
            response.raise_for_status()

            # 解析API返回结果
            invoice_info = parse_invoice_info(response.json())

            return invoice_info
        except requests.exceptions.ReadTimeout:
            if retry < max_retries - 1:
                print(f"请求超时，第 {retry + 1} 次重试，等待 {retry_delay} 秒后重试...")
                time.sleep(retry_delay)
            else:
                print("达到最大重试次数，请求失败。")
                raise
        except requests.exceptions.HTTPError as e:
            print(f"HTTP错误: {str(e)}")
            raise
        except Exception as e:
            print(f"API调用失败: {str(e)}")
            raise

# 在 parse_invoice_info 函数中添加调试信息
def parse_invoice_info(result):
    try:
        content = result["choices"][0]["message"]["content"]
        print(f"原始API响应内容:\n{content}")

        invoice_info = {}
        patterns = {
            "发票号码": r"发票号码[:：]\s*(\d{19,20})",
            "开票日期": r"开票日期[:：]\s*(\d{4}[年\-]\d{1,2}[月\-]\d{1,2}日?)",
            "销售方名称": r"销[\s\S]*?名称[:：]\s*([^：\n]+)",
            "纳税人识别号": r"(?:纳税人识别号|统一社会信用代码)[:：][\s\S]*?([A-Z0-9]{18})",
            "合计金额": r"价税合计[\s\S]*?小写[\s\S]*?[￥¥]?\s*([\d,]+\.\d{2})"
        }

        for key, pattern in patterns.items():
            match = re.search(pattern, content, re.DOTALL)
            if match:
                value = match.group(1).strip()
                if key == "开票日期":
                    value = value.replace('年', '-').replace('月', '-').replace('日', '')
                    value = re.sub(r'[^0-9\-]', '', value)  # 去除非日期字符
                if key == "合计金额":
                    value = re.sub(r"[^\d.]", "", value)  # 清理非法字符
                invoice_info[key] = value

        # 金额转换为Decimal
        raw_amount = invoice_info.get("合计金额", "0").replace(',', '')
        try:
            invoice_info["合计金额"] = Decimal(raw_amount)
            print(f"[DEBUG] 金额转换成功: {invoice_info['合计金额']}")
        except InvalidOperation:
            print(f"金额转换失败: '{raw_amount}'")
            invoice_info["合计金额"] = Decimal('0.00')

        # 打印解析后的 invoice_id
        print(f"[DEBUG] 解析后的发票号码: {invoice_info.get('发票号码')}")
        return invoice_info
    except Exception as e:
        print(f"解析失败: {str(e)}\n原始内容片段：{content[:300]}")
        return {
            "发票号码": "未知_" + str(uuid.uuid4())[:8],
            "开票日期": datetime.now().strftime('%Y-%m-%d'),
            "销售方名称": "未知公司",
            "纳税人识别号": "",
            "合计金额": 0.0,
            "发票类型": "增值税普通发票"
        }

# 在 save_invoice_info 函数中添加调试信息
def save_invoice_info(invoice_info, image_id, filename):
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()

        # 验证必填字段
        mandatory_fields = {
            'invoice_id': invoice_info.get("发票号码") or f"UNKNOWN_{int(time.time())}",
            'issue_date': invoice_info.get("开票日期") or datetime.now().date(),
            'selling_company': invoice_info.get("销售方名称", "未知公司")[:100],  # 限制长度
            'selling_id': invoice_info.get("纳税人识别号", "")[:20],
            'amount': str(invoice_info.get("合计金额", "0.00")),
            'type': invoice_info.get("发票类型", "增值税普通发票"),
            'image_id': image_id,
            'filename': filename  # 添加文件名字段
        }
        # 打印即将插入数据库的 invoice_id 和 filename
        print(f"[DEBUG] 即将插入数据库的发票号码: {mandatory_fields['invoice_id']}")
        print(f"[DEBUG] 插入的图片文件名: {mandatory_fields['filename']}")  # 确认文件名
        print(f"[DEBUG] 插入金额值: {mandatory_fields['amount']}")  # 确认值格式

        cursor.execute(
            """
            INSERT INTO invoices (
                invoice_id, issue_date, selling_company, 
                selling_id, amount, type, image_id, filename
            ) VALUES (%(invoice_id)s, %(issue_date)s, %(selling_company)s, 
                     %(selling_id)s, %(amount)s, %(type)s, %(image_id)s, %(filename)s)
            """,
            mandatory_fields
        )
        conn.commit()
        print(f"成功保存：{mandatory_fields}")

    except mysql.connector.Error as err:
        print(f"数据库错误（{err.errno}）：{err.msg}")
        # 应急存储机制
        with open('failed_records.log', 'a') as f:
            f.write(f"{datetime.now()} | {mandatory_fields}\n")
    finally:
        cursor.close()
        conn.close()

@app.route('/api/invoices', methods=['GET'])
def query_invoices():
    try:
        invoice_id = request.args.get('invoice_id', '').strip()
        selling_company = request.args.get('selling_company', '').strip()
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')

        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        query = "SELECT * FROM invoices WHERE 1=1"
        params = []

        if invoice_id:
            query += " AND invoice_id LIKE %s"
            params.append(f"{invoice_id}%")

        if selling_company:
            query += " AND selling_company LIKE %s"
            params.append(f"%{selling_company}%")

        if start_date and end_date:
            query += " AND issue_date BETWEEN %s AND %s"
            params.extend([start_date, end_date])

        cursor.execute(query, params)
        rows = cursor.fetchall()

        return jsonify({
            'success': True,
            'invoices': rows,
            'total_records': len(rows),
            'current_page': 1,
            'total_pages': 1
        })
    except Exception as e:
        print(f"[ERROR] 查询失败: {str(e)}")
        return jsonify({'success': False, 'message': '查询失败'}), 500
    finally:
        cursor.close()
        conn.close()

@app.route('/api/invoice_stats', methods=['GET'])
def invoice_stats():
    try:
        selling_company = request.args.get('selling_company', '').strip()
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')

        if start_date and end_date:
            try:
                start_date = datetime.strptime(start_date, '%Y-%m-%d').date()
                end_date = datetime.strptime(end_date, '%Y-%m-%d').date()
            except ValueError:
                return jsonify({'success': False, 'message': '日期格式不正确'}), 400

        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor(dictionary=True)

        query = "SELECT COUNT(*) as total_count, SUM(amount) as total_amount FROM invoices WHERE 1=1"
        params = []

        if start_date and end_date:
            query += " AND issue_date BETWEEN %s AND %s"
            params.extend([start_date, end_date])

        if selling_company:
            query += " AND selling_company LIKE %s"
            params.append(f"%{selling_company}%")

        cursor.execute(query, params)
        result = cursor.fetchone()

        if result:
            total_count = result['total_count']
            total_amount = result['total_amount'] if result['total_amount'] is not None else 0.0
            cursor.execute("SELECT * FROM invoices WHERE 1=1" + (" AND issue_date BETWEEN %s AND %s" if start_date and end_date else "") + (" AND selling_company LIKE %s" if selling_company else ""), params)
            details = cursor.fetchall()
            return jsonify({
                'success': True,
                'total_count': total_count,
                'total_amount': float(total_amount),
                'details': details
            })
        else:
            return jsonify({'success': False, 'message': '没有找到符合条件的发票'}), 404
    except Exception as e:
        print(f"[ERROR] 统计失败: {str(e)}")
        return jsonify({'success': False, 'message': '统计失败'}), 500
    finally:
        cursor.close()
        conn.close()

@app.route('/api/profile', methods=['POST'])
def save_profile():
    try:
        data = request.json
        company_name = data.get('company_name', '')
        email = data.get('email', '')
        phone = data.get('phone', '')
        address = data.get('address', '')
        bank_name = data.get('bank_name', '')
        bank_account = data.get('bank_account', '')

        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        cursor.execute(
            """
            UPDATE users SET 
                company_name = %s, 
                email = %s, 
                phone = %s, 
                address = %s, 
                bank_name = %s, 
                bank_account = %s
            WHERE id = 1  
            """,
            (company_name, email, phone, address, bank_name, bank_account)
        )
        conn.commit()
        cursor.close()
        conn.close()

        return jsonify({'success': True, 'message': '个人信息保存成功'})
    except Exception as e:
        print(f"Error: {str(e)}")
        return jsonify({'success': False, 'message': '保存过程中发生错误'}), 500

# 提供上传文件的路由
@app.route('/uploads/<filename>')
def uploaded_file(filename):
    file_path = os.path.join(app.config['UPLOAD_FOLDER'], filename)
    if os.path.exists(file_path):
        return send_from_directory(app.config['UPLOAD_FOLDER'], filename)
    else:
        return jsonify({'success': False, 'message': '文件不存在'}), 404

# 首页
@app.route('/')
def index():
    return render_template('index.html')

if __name__ == '__main__':
    app.run(debug=True)